﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
namespace j1
{
    public class Database
    {
        private const string ConnectionString = "Data Source=school.db;Version=3;";

        public static void Initialize()
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                string schoolTable = @"CREATE TABLE IF NOT EXISTS School (
                                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                    Name TEXT NOT NULL)";
                string classTable = @"CREATE TABLE IF NOT EXISTS Class (
                                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                    Name TEXT NOT NULL,
                                    SchoolId INTEGER NOT NULL,
                                    FOREIGN KEY (SchoolId) REFERENCES School(Id))";
                string studentTable = @"CREATE TABLE IF NOT EXISTS Student (
                                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                    Name TEXT NOT NULL,
                                    ClassId INTEGER NOT NULL,
                                    FOREIGN KEY (ClassId) REFERENCES Class(Id))";
                string logTable = @"CREATE TABLE IF NOT EXISTS Log (
                                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                Action TEXT NOT NULL,
                                Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)";

                using (var command = new SQLiteCommand(schoolTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(classTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(studentTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(logTable, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static SQLiteConnection GetConnection()
        {
            return new SQLiteConnection(ConnectionString);
        }

        public static void LogAction(string action)
        {
            using (var connection = GetConnection())
            {
                connection.Open();
                string query = "INSERT INTO Log (Action) VALUES (@action)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@action", action);
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}




